<?php


namespace App\Exports;


use App\Models\PartCompany;
use App\Models\PartCompanyClassify;
use App\Models\ProjectClassify;
use App\Models\SortLink;
use App\Services\BaseService;
use App\Services\CommonService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithCustomStartCell;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithTitle;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\PageMargins;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * 导出一个【项目里面的全部案卷目录】
 *
 * Class BookTitleExport29
 * @package App\Exports
 * 8文号案卷目录导出格式
 */
class ProjectBookTitleExport8 implements  FromCollection, WithTitle, WithColumnWidths, WithStyles,  WithCustomStartCell
{
    use Exportable;

    protected  $totalRow; //总行数
    protected  $projectId; //参见单位
    protected  $partCompanyId; //参见单位id(选填)，如果存在，则代表导出指定参见单位下的全部案卷
    protected  $projectClassifyId; //单位工程id，如果存在，则代表导出指定单位工程下的全部案卷
    protected  $sheetName;
    protected  $parentNodeId;

    public function __construct($sheetName, $projectId, $partCompanyId = 0, $parentNodeId = 0)
    {
        $this->sheetName     = $sheetName;
        $this->projectId     = $projectId;
        $this->partCompanyId = $partCompanyId;
        $this->parentNodeId  = $parentNodeId; //一个有下级的单位工程ID
    }

    /**
     * 获取数据集合
     * $partCompany = PartCompany::query()->where('id', $partCompanyId)->value('title') ?: '未知';
     * $projectClassify = ProjectClassify::query()->where('id', $projectClassifyId)->value('title') ?: '未知';
     */
    public function collection()
    {
        $resData            = CommonService::getIdsByProjectId($this->projectId, $this->partCompanyId);
        $partCompanyIds     = $resData['part_company_ids'];
        $projectClassifyIds = $resData['project_classify_ids'];

        //下面这一些是导出一个有下级的单位工程用的，把范围限定在下级单位工程
        $redisKey = "childrenBts:{$this->partCompanyId}:{$this->parentNodeId}";
        $tables = redis()->smembers($redisKey); //获取当前参建单位下的案卷全部的分表
        $childrenProClassifyIds = [];
        foreach ($tables as $table) {
            $ids = CommonService::getTableParams($table);
            $childrenProClassifyIds[] = $ids['id2'];
        }

        $r_list = [];
        foreach ($partCompanyIds as $partCompanyId) {
            $partCompanyName = self::getPartCompanyName($partCompanyId);
            /**
             * 导出参建单位案卷，注意把单位工程按照顺序从上到下排好,如果有排序数据，要优先使用排序数据，
             * 排序数据会在  ProjectClassifyController 的 treeList方法里面生成
             */
            $projectClassifyIdsSL = SortLink::query()
                ->when($childrenProClassifyIds, function ($query) use ($childrenProClassifyIds) {
                    $query->whereIn('project_classify_id', $childrenProClassifyIds);
                })
                ->where('part_company_id', $partCompanyId)
                ->orderBy('id')
                ->pluck('project_classify_id');
            if ($projectClassifyIdsSL->isNotEmpty()) {
                $projectClassifyIds = $projectClassifyIdsSL->toArray();
            }
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table)) { //如果有表
                    $count = DB::table($table)->whereNull('deleted_at')->count('id');
                    if (!empty($count)) { //如果表有数据
                        $bookTitleInstance = CommonService::getBookTitleSearchInstance($partCompanyId, $projectClassifyId);
                        $list = $bookTitleInstance
                            ->select('id', 'archives_no', 'title', 'blueprint', 'base_map', 'volume', 'page_num',
                                'years', 'store_period', 'secret_level', 'cabin_no', 'remark', 'mixed_no')
                            ->orderBy('serial_no')
                            ->orderBy('id')
                            ->get();
                        if ($list->isNotEmpty()) {
                            $projectClassifyName = self::getProjectClassifyName($projectClassifyId);
                            $list = $list->toArray();
                            foreach ($list as $key => $val) {
                                $arr['number']           = $key + 1;              //序号
                                $arr['archives_no']      = $val['archives_no'];   //档案号
                                $arr['title']            = htmlspecialchars_decode($val['title']); //案卷题名
                                $arr['blueprint']        = $val['blueprint'];     //蓝图
                                $arr['base_map']         = $val['base_map'];      //底图
                                $arr['volume']           = $val['volume'];        //册
                                $arr['page_num']         = $val['page_num'];      //张
                                $arr['years']            = $val['years'];         //年代
                                $arr['store_period']     = $val['store_period'];  //保管期限
                                $arr['secret_level']     = $val['secret_level'];  //保密程度
                                $arr['cabin_no']         = $val['cabin_no'];      //柜号
                                $arr['remark']           = $val['remark'];        //备注
                                $arr['mixed_no']         = $val['mixed_no'];      //互见号
                                $arr['part_company']     = $partCompanyName;      //形成单位
                                $arr['project_classify'] = $projectClassifyName;
                                $arr['data_count']       = CommonService::getBookCatalogInstance($this->partCompanyId, $projectClassifyId)->where('book_title_id', $val['id'])->count() ?: 0;
                                $r_list[]                = $arr;
                            }
                        }
                    }
                }
            }
        }
        $this->totalRow = count($r_list) + 3;

        return collect($r_list);
    }

    /**
     * 获取参建单位名字
     */
    public static function getPartCompanyName($partCompanyId)
    {
        $redisKey = 'PartCompanyId:' . $partCompanyId;
        $name = redis()->get($redisKey);
        if (empty($name)) {
            $name = PartCompany::query()->where('id', $partCompanyId)->value('title');
            redis()->setex($redisKey, 30 * 3600, $name);
        }
        return $name;
    }

    /**
     * 获取单位工程的名字
     */
    public static function getProjectClassifyName($id)
    {
        $redisKey = 'ProjectClassifyId:' . $id;
        $name = redis()->get($redisKey);
        if (empty($name)) {
            $name = ProjectClassify::query()->where('id', $id)->value('title');
            redis()->setex($redisKey, 600, $name);
        }
        return $name;
    }


    /**
     * sheet名称
     * @return string
     */
    public function title(): string
    {
        return $this->sheetName . '全部案卷';
    }

    /**
     * 设置列宽
     * @return array
     */
    public function columnWidths(): array
    {
        return [
            'A' => 8, 'B' => 20, 'C' => 50, 'D'=>8, 'E'=>8, 'F'=>8, 'G'=>8, 'H'=>8, 'I'=>8, 'J'=>8, 'K'=>8, 'L'=>15, 'M'=>8, 'N'=>35, 'O'=>35, 'P'=> 7
        ];
    }

    /**
     * 从哪个单元格开始渲染数据，数据集合如 $list
     * @return string
     */
    public function startCell(): string
    {
        return 'A4';
    }

    /**
     * 样式设置
     * @param Worksheet $sheet
     * @throws PhpSpreadsheetException
     */
    public function styles(Worksheet $sheet)
    {
        /**
         * 设置为 A4纸
         */
        $pageSetup = new PageSetup();
        $pageSetup->setPaperSize(9);
        $sheet->setPageSetup($pageSetup);
        /**
         * 设置页边距
         */
        $pageMargins = new PageMargins();
        $pageMargins->setTop(0.2);
        $pageMargins->setRight(0.3);
        $pageMargins->setLeft(0.5);
        $sheet->setPageMargins($pageMargins);

        $sheet->getDefaultRowDimension()->setRowHeight(40); //设置行高(全部行高)

        $sheet->getStyle('A1:G'.$this->totalRow)->getFont()->setName('宋体'); //宋体
        $sheet->getStyle('C4:G'.$this->totalRow)->getFont()->setSize(11); //大小

        $sheet->mergeCells('A1:P1');
        $sheet->setCellValue('A1', '科 技 档 案 案 卷 目 录');

        $sheet->mergeCells('A2:A3');
        $sheet->setCellValue('A2', '顺
号');

        $sheet->mergeCells('B2:B3');
        $sheet->setCellValue('B2', '档案号');

        $sheet->mergeCells('C2:C3');
        $sheet->setCellValue('C2', '名称');

        $sheet->mergeCells('D2:E2');
        $sheet->setCellValue('D2', '种类');
        $sheet->setCellValue('D3', '蓝图');
        $sheet->setCellValue('E3', '底图');

        $sheet->mergeCells('F2:G2');
        $sheet->setCellValue('F2', '种类');
        $sheet->setCellValue('F3', '册');
        $sheet->setCellValue('G3', '张');

        $sheet->mergeCells('H2:H3');
        $sheet->setCellValue('H2', '所属
年代');

        $sheet->mergeCells('I2:I3');
        $sheet->setCellValue('I2', '保管
期限');

        $sheet->mergeCells('J2:J3');
        $sheet->setCellValue('J2', '机密
程度');

        $sheet->mergeCells('K2:K3'); //柜号
        $sheet->setCellValue('K2', '柜号');

        $sheet->mergeCells('L2:L3');
        $sheet->setCellValue('L2', '备注');

        $sheet->mergeCells('M2:M3');
        $sheet->setCellValue('M2', '互见号');

        $sheet->mergeCells('N2:N3');
        $sheet->setCellValue('N2', '形成单位');

        $sheet->mergeCells('O2:O3');
        $sheet->setCellValue('O2', '单位工程');

        $sheet->mergeCells('P2:P3');
        $sheet->setCellValue('P2', '卷内数量');

        /**
         * 顶部案卷目录样式设置
         */
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setVertical('center');//垂直居中
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setHorizontal('center');//水平居中
        $sheet->getStyle('A1:P' . $this->totalRow)->getAlignment()->setWrapText(true); //自动换行

        $sheet->getStyle('A1')->getFont()->setSize(22); //大小
        $sheet->getStyle('A1:P3')->getFont()->setBold(true); //粗体

        /**
         * 设置边框
         */
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' =>  Border::BORDER_THIN //细边框
                ]
            ]
        ];
        $sheet->getStyle('A2:P'.$this->totalRow)->applyFromArray($styleArray);
    }

}
